package com.kaifamiao.mapper;

import com.kaifamiao.model.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

/**
 * @author lucien-ma
 * @date 2021/8/17
 * @apiNote
 */
public interface IUserMapper {

    /**
     * 查询所有用户
     * @return
     */
    @Select("select * from users")
    @Results(id = "userResult", value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "username", property = "name"),
            @Result(column = "password", property = "password")
    })
    List<User> findAll();

    /**
     * 根据 id 查询一条记录
     * @param id
     * @return
     */
    @Select("select * from users where id = #{id}")
    @ResultMap("userResult")
    User findById(Integer id);

    /**
     * 根据姓名和密码进行查找
     *
     * 多个参数时， 可以使用 arg+下标取值，下标从 0 开始
     * * 也可以用 param+序号取值， 序号从 1 开始
     * * '@Param' 可以设置参数的名称
     *    User findByNameAndPassword(@Param("name") String name, @Param("password") String password);
     *    就可以这样写：
     *    @Select("select * from users where username = #{arg0} and password = #{param2}")
     *
     * @param name
     * @param password
     * @return
     */
    @Select("select * from users where username = #{arg0} and password = #{param2}")
    @ResultMap("userResult")
    User findByNameAndPassword(String name, String password);

    /**
     * 向数据库中添加一条数据
     * @param user
     * @return
     */
    @Insert("insert into users(username, password) values(#{name}, #{password})")
    int insert(User user);

    /**
     * 向数据库添加一条数据，并且把自增的 id 返回
     * @param user
     * @return
     */
    @Insert("insert into users(username, password) values(#{name}, #{password})")
    @SelectKey(statement = "select last_insert_id()", before = false, keyProperty = "id",
            resultType = Integer.class)
    int insert2(User user);

    /**
     * 目前数据库结构中 id 是int类型所以此方法执行会报错
     * 此处只做知识点讲解
     * @param user
     * @return
     */
    @Insert("insert into users(id, username, password) values(#{id}, #{name},#{password})")
    @SelectKey(statement = "select UUID()", before = true, keyProperty = "id", resultType = String.class)
    int insert3(User user);

    /**
     * 根据 id 修改一条记录
     * @param user
     * @return
     */
    @Update("update users set username = #{name}, password = #{password} where id = #{id}")
    int update(User user);

    /**
     * 根据 id 删除一条数据
     * @param id
     * @return
     */
    @Delete("delete from users where id = #{id}")
    int deleteById(Integer id);

    /**
     * 使用聚合函数，统计users表的记录条数
     * @return
     */
    @Select("select count(id) from users")
    int count();

    /**
     * 根据姓名模糊查询
     * @param value
     * @return
     */
    @Select("select * from users where username like '%${value}%'")
    @ResultMap("userResult")
    List<User> findAllNameLikeValue(String value);

    /**
     * 查询所用用户信息，并查询出该用户名下的所有的账户
     * 一对多关系
     * @return
     */
    @Select("select * from users")
    @Results(id = "manyMap", value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "username", property = "name"),
            @Result(column = "password", property = "password"),
            @Result(column = "id", property = "accountList",
            many = @Many(select = "com.kaifamiao.mapper.IAccountMapper.selectByUid", fetchType = FetchType.LAZY))
    })
    List<User> findAllWithAccounts();

    /**
     * 根据用户编号查询其所有账户信息
     * @param id
     * @return
     */
    @Select("select * from users where id = #{id}")
    @ResultMap("manyMap")
    User findByIdWithAccounts(Integer id);

}
